{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "█\r"
     ]
    }
   ],
   "source": [
    "from setting import get_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = get_engine('db_stock','local')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (1366, \"Incorrect string value: '\\\\xD6\\\\xD0\\\\xB9\\\\xFA\\\\xB1\\\\xEA...' for column 'VARIABLE_VALUE' at row 480\")\n",
      "  result = self._query(query)\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_sql('tb_bond_jisilu',con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>可转债代码</th>\n",
       "      <th>可转债名称</th>\n",
       "      <th>可转债价格</th>\n",
       "      <th>正股名称</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股现价</th>\n",
       "      <th>正股涨跌幅</th>\n",
       "      <th>最新转股价</th>\n",
       "      <th>溢价率</th>\n",
       "      <th>可转债涨幅</th>\n",
       "      <th>回售触发价</th>\n",
       "      <th>转股起始日</th>\n",
       "      <th>到期时间</th>\n",
       "      <th>成交额(万元)</th>\n",
       "      <th>强赎价格</th>\n",
       "      <th>剩余时间</th>\n",
       "      <th>回售起始日</th>\n",
       "      <th>评级</th>\n",
       "      <th>发行时间</th>\n",
       "      <th>强制赎回条款</th>\n",
       "      <th>下修条件</th>\n",
       "      <th>下修提示</th>\n",
       "      <th>回售</th>\n",
       "      <th>下调次数</th>\n",
       "      <th>转债剩余占总市值比</th>\n",
       "      <th>剩余规模</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>股东配售率</th>\n",
       "      <th>更新日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>127008</td>\n",
       "      <td>特发转债</td>\n",
       "      <td>185.60</td>\n",
       "      <td>特发信息</td>\n",
       "      <td>000070</td>\n",
       "      <td>16.29</td>\n",
       "      <td>3.82</td>\n",
       "      <td>6.78</td>\n",
       "      <td>-22.75</td>\n",
       "      <td>1.87</td>\n",
       "      <td>4.75</td>\n",
       "      <td>2019-05-22</td>\n",
       "      <td>23-11-16</td>\n",
       "      <td>124536.93</td>\n",
       "      <td>108.0</td>\n",
       "      <td>4.682</td>\n",
       "      <td>2021-11-16</td>\n",
       "      <td>AA</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>在本次发行的可转债转股期内，如果公司 A 股股票连续三十个交易日中至少有十五个交易日的收盘价...</td>\n",
       "      <td>在本次发行的可转债存续期间，当公司 A 股股票在任意连续二十个交易日中至少有十个交易日的收盘...</td>\n",
       "      <td></td>\n",
       "      <td>本次发行的可转债最后两个计息年度，如果公司股票在任何连续三十个交易日的收盘价低于当期转股价格...</td>\n",
       "      <td>0</td>\n",
       "      <td>4.1</td>\n",
       "      <td>4.194</td>\n",
       "      <td>4.194</td>\n",
       "      <td>45.92</td>\n",
       "      <td>2019-03-13 15:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>110050</td>\n",
       "      <td>佳都转债</td>\n",
       "      <td>135.49</td>\n",
       "      <td>佳都科技</td>\n",
       "      <td>600728</td>\n",
       "      <td>11.90</td>\n",
       "      <td>-3.02</td>\n",
       "      <td>7.95</td>\n",
       "      <td>-9.49</td>\n",
       "      <td>-2.99</td>\n",
       "      <td>5.57</td>\n",
       "      <td>2019-06-25</td>\n",
       "      <td>24-12-18</td>\n",
       "      <td>43709.08</td>\n",
       "      <td>109.0</td>\n",
       "      <td>5.773</td>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>AA</td>\n",
       "      <td>2018-12-19</td>\n",
       "      <td>在本次发行的可转换公司债券转股期内，如果公司 A 股股票连续 30 个交易日中至少有 15 ...</td>\n",
       "      <td>在本次发行的可转换公司债券存续期间，当公司 A 股股票在任意连续 20 个交易日中至少有 1...</td>\n",
       "      <td></td>\n",
       "      <td>本次发行的可转换公司债券最后两个计息年度，如果公司 A 股股票在任何连续 30 个交易日的收...</td>\n",
       "      <td>0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.747</td>\n",
       "      <td>8.747</td>\n",
       "      <td>14.08</td>\n",
       "      <td>2019-03-13 15:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>128053</td>\n",
       "      <td>尚荣转债</td>\n",
       "      <td>119.50</td>\n",
       "      <td>尚荣医疗</td>\n",
       "      <td>002551</td>\n",
       "      <td>6.41</td>\n",
       "      <td>-4.33</td>\n",
       "      <td>4.94</td>\n",
       "      <td>-7.91</td>\n",
       "      <td>-2.07</td>\n",
       "      <td>3.46</td>\n",
       "      <td>2019-08-20</td>\n",
       "      <td>25-02-14</td>\n",
       "      <td>17487.95</td>\n",
       "      <td>110.0</td>\n",
       "      <td>5.932</td>\n",
       "      <td>2023-02-14</td>\n",
       "      <td>AA</td>\n",
       "      <td>2019-02-14</td>\n",
       "      <td>（1）在转股期内，如果公司 A 股股票连续 30 个交易日中至少有 15 个交易日的收盘价格...</td>\n",
       "      <td>在本次发行的可转换公司债券存续期间，当公司股票在任意连续三十个交易日中至少有十五个交易日的收...</td>\n",
       "      <td></td>\n",
       "      <td>在本次发行的可转换公司债券最后两个计息年度，如果公司股票在任何连续30 个交易日的收盘价格低...</td>\n",
       "      <td>0</td>\n",
       "      <td>16.6</td>\n",
       "      <td>7.500</td>\n",
       "      <td>7.500</td>\n",
       "      <td>15.80</td>\n",
       "      <td>2019-03-13 15:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123018</td>\n",
       "      <td>溢利转债</td>\n",
       "      <td>114.45</td>\n",
       "      <td>溢多利</td>\n",
       "      <td>300381</td>\n",
       "      <td>10.27</td>\n",
       "      <td>-1.15</td>\n",
       "      <td>8.41</td>\n",
       "      <td>-6.28</td>\n",
       "      <td>-1.34</td>\n",
       "      <td>5.89</td>\n",
       "      <td>2019-06-26</td>\n",
       "      <td>24-12-20</td>\n",
       "      <td>2240.50</td>\n",
       "      <td>110.0</td>\n",
       "      <td>5.778</td>\n",
       "      <td>2022-12-20</td>\n",
       "      <td>AA-</td>\n",
       "      <td>2018-12-20</td>\n",
       "      <td>在转股期内，公司股票在任意连续三十个交易日中至少十五个交易日的收盘价格不低于当期转股价格的 ...</td>\n",
       "      <td>在本次发行的可转换公司债券存续期间，当公司股票在任意连续三十个交易日中至少有十五个交易日的收...</td>\n",
       "      <td></td>\n",
       "      <td>在本次发行的可转换公司债券最后两个计息年度内，如果公司股票在任何连续三十个交易日收盘价格低于...</td>\n",
       "      <td>0</td>\n",
       "      <td>15.9</td>\n",
       "      <td>6.650</td>\n",
       "      <td>6.650</td>\n",
       "      <td>35.63</td>\n",
       "      <td>2019-03-13 15:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>123016</td>\n",
       "      <td>洲明转债</td>\n",
       "      <td>135.64</td>\n",
       "      <td>洲明科技</td>\n",
       "      <td>300232</td>\n",
       "      <td>13.63</td>\n",
       "      <td>0.07</td>\n",
       "      <td>9.45</td>\n",
       "      <td>-5.96</td>\n",
       "      <td>-2.26</td>\n",
       "      <td>6.62</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>24-11-07</td>\n",
       "      <td>1797.93</td>\n",
       "      <td>110.0</td>\n",
       "      <td>5.660</td>\n",
       "      <td>2022-11-07</td>\n",
       "      <td>AA-</td>\n",
       "      <td>2018-11-07</td>\n",
       "      <td>在本次发行的可转换公司债券转股期内，如果公司 A 股股票连续 30 个交易日中至少有 15 ...</td>\n",
       "      <td>在本次发行的可转债存续期间，当公司股票在任意连续三十个交易日中至少有十五个交易日的收盘价低于...</td>\n",
       "      <td></td>\n",
       "      <td>在本次发行的可转换公司债券最后两个计息年度，如果公司股票在任何连续30 个交易日的收盘价格低...</td>\n",
       "      <td>0</td>\n",
       "      <td>5.3</td>\n",
       "      <td>5.480</td>\n",
       "      <td>5.480</td>\n",
       "      <td>33.98</td>\n",
       "      <td>2019-03-13 15:31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    可转债代码 可转债名称   可转债价格  正股名称    正股代码   正股现价        ...         下调次数  转债剩余占总市值比   剩余规模   发行规模  股东配售率              更新日期\n",
       "0  127008  特发转债  185.60  特发信息  000070  16.29        ...            0        4.1  4.194  4.194  45.92  2019-03-13 15:31\n",
       "1  110050  佳都转债  135.49  佳都科技  600728  11.90        ...            0        4.5  8.747  8.747  14.08  2019-03-13 15:31\n",
       "2  128053  尚荣转债  119.50  尚荣医疗  002551   6.41        ...            0       16.6  7.500  7.500  15.80  2019-03-13 15:31\n",
       "3  123018  溢利转债  114.45   溢多利  300381  10.27        ...            0       15.9  6.650  6.650  35.63  2019-03-13 15:31\n",
       "4  123016  洲明转债  135.64  洲明科技  300232  13.63        ...            0        5.3  5.480  5.480  33.98  2019-03-13 15:31\n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "zg_list = list(df['正股代码'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['000070',\n",
       " '600728',\n",
       " '002551',\n",
       " '300381',\n",
       " '300232',\n",
       " '603305',\n",
       " '002783',\n",
       " '000811',\n",
       " '603313',\n",
       " '603626',\n",
       " '002131',\n",
       " '603668',\n",
       " '600567',\n",
       " '601311',\n",
       " '300601',\n",
       " '300057',\n",
       " '300058',\n",
       " '002446',\n",
       " '300409',\n",
       " '600372',\n",
       " '601128',\n",
       " '300059',\n",
       " '603228',\n",
       " '002548',\n",
       " '002224',\n",
       " '000665',\n",
       " '603323',\n",
       " '300297',\n",
       " '600031',\n",
       " '300197',\n",
       " '300539',\n",
       " '600831',\n",
       " '603421',\n",
       " '603797',\n",
       " '600233',\n",
       " '002839',\n",
       " '002787',\n",
       " '002807',\n",
       " '603518',\n",
       " '300388',\n",
       " '300473',\n",
       " '601727',\n",
       " '603677',\n",
       " '002793',\n",
       " '603569',\n",
       " '601012',\n",
       " '603568',\n",
       " '603588',\n",
       " '603023',\n",
       " '300568',\n",
       " '002815',\n",
       " '002100',\n",
       " '603055',\n",
       " '002142',\n",
       " '601929',\n",
       " '601127',\n",
       " '600483',\n",
       " '002728',\n",
       " '002658',\n",
       " '300407',\n",
       " '002865',\n",
       " '600690',\n",
       " '603816',\n",
       " '000001',\n",
       " '601233',\n",
       " '603019',\n",
       " '002758',\n",
       " '603345',\n",
       " '600908',\n",
       " '300618',\n",
       " '601818',\n",
       " '002179',\n",
       " '603989',\n",
       " '600863',\n",
       " '002318',\n",
       " '600755',\n",
       " '002823',\n",
       " '002562',\n",
       " '603179',\n",
       " '000783',\n",
       " '002741',\n",
       " '600183',\n",
       " '601211',\n",
       " '603618',\n",
       " '002013',\n",
       " '603738',\n",
       " '000861',\n",
       " '601966',\n",
       " '603225',\n",
       " '000623',\n",
       " '600998',\n",
       " '300422',\n",
       " '600566',\n",
       " '002273',\n",
       " '002258',\n",
       " '002717',\n",
       " '603601',\n",
       " '002271',\n",
       " '002753',\n",
       " '603027',\n",
       " '002078',\n",
       " '600398',\n",
       " '002542',\n",
       " '601238',\n",
       " '002472',\n",
       " '002597',\n",
       " '002008',\n",
       " '002772',\n",
       " '600185',\n",
       " '002279',\n",
       " '603822',\n",
       " '601222',\n",
       " '002460',\n",
       " '002707',\n",
       " '002284',\n",
       " '002527',\n",
       " '600271',\n",
       " '002245',\n",
       " '300433',\n",
       " '000700',\n",
       " '002325',\n",
       " '002496']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "basic_df = ts.get_stock_basics()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>area</th>\n",
       "      <th>pe</th>\n",
       "      <th>outstanding</th>\n",
       "      <th>totals</th>\n",
       "      <th>totalAssets</th>\n",
       "      <th>liquidAssets</th>\n",
       "      <th>fixedAssets</th>\n",
       "      <th>reserved</th>\n",
       "      <th>...</th>\n",
       "      <th>bvps</th>\n",
       "      <th>pb</th>\n",
       "      <th>timeToMarket</th>\n",
       "      <th>undp</th>\n",
       "      <th>perundp</th>\n",
       "      <th>rev</th>\n",
       "      <th>profit</th>\n",
       "      <th>gpr</th>\n",
       "      <th>npr</th>\n",
       "      <th>holders</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>300116</th>\n",
       "      <td>坚瑞沃能</td>\n",
       "      <td>电气设备</td>\n",
       "      <td>陕西</td>\n",
       "      <td>0.00</td>\n",
       "      <td>12.94</td>\n",
       "      <td>24.33</td>\n",
       "      <td>1566351.50</td>\n",
       "      <td>1150821.75</td>\n",
       "      <td>238616.80</td>\n",
       "      <td>490198.44</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.14</td>\n",
       "      <td>-14.68</td>\n",
       "      <td>20100902</td>\n",
       "      <td>-766017.63</td>\n",
       "      <td>-3.15</td>\n",
       "      <td>-90.40</td>\n",
       "      <td>-69.27</td>\n",
       "      <td>24.37</td>\n",
       "      <td>-503.89</td>\n",
       "      <td>68130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000677</th>\n",
       "      <td>恒天海龙</td>\n",
       "      <td>化纤</td>\n",
       "      <td>山东</td>\n",
       "      <td>1010.84</td>\n",
       "      <td>8.64</td>\n",
       "      <td>8.64</td>\n",
       "      <td>88712.68</td>\n",
       "      <td>34382.80</td>\n",
       "      <td>45130.65</td>\n",
       "      <td>69768.81</td>\n",
       "      <td>...</td>\n",
       "      <td>0.32</td>\n",
       "      <td>13.34</td>\n",
       "      <td>19961226</td>\n",
       "      <td>-149955.27</td>\n",
       "      <td>-1.74</td>\n",
       "      <td>32.77</td>\n",
       "      <td>287.29</td>\n",
       "      <td>15.19</td>\n",
       "      <td>0.45</td>\n",
       "      <td>77347.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300152</th>\n",
       "      <td>科融环境</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>江苏</td>\n",
       "      <td>9.27</td>\n",
       "      <td>7.13</td>\n",
       "      <td>7.13</td>\n",
       "      <td>195568.00</td>\n",
       "      <td>99731.43</td>\n",
       "      <td>19260.75</td>\n",
       "      <td>50605.01</td>\n",
       "      <td>...</td>\n",
       "      <td>1.11</td>\n",
       "      <td>4.12</td>\n",
       "      <td>20101229</td>\n",
       "      <td>-47372.92</td>\n",
       "      <td>-0.66</td>\n",
       "      <td>0.43</td>\n",
       "      <td>686.96</td>\n",
       "      <td>19.61</td>\n",
       "      <td>113.39</td>\n",
       "      <td>61878.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>001896</th>\n",
       "      <td>豫能控股</td>\n",
       "      <td>火力发电</td>\n",
       "      <td>河南</td>\n",
       "      <td>25.91</td>\n",
       "      <td>9.30</td>\n",
       "      <td>11.51</td>\n",
       "      <td>2086533.63</td>\n",
       "      <td>445571.75</td>\n",
       "      <td>1194374.75</td>\n",
       "      <td>500207.81</td>\n",
       "      <td>...</td>\n",
       "      <td>4.99</td>\n",
       "      <td>0.81</td>\n",
       "      <td>19980122</td>\n",
       "      <td>-58811.98</td>\n",
       "      <td>-0.51</td>\n",
       "      <td>-21.34</td>\n",
       "      <td>122.22</td>\n",
       "      <td>9.47</td>\n",
       "      <td>2.36</td>\n",
       "      <td>34008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600095</th>\n",
       "      <td>哈高科</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>黑龙江</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.61</td>\n",
       "      <td>3.61</td>\n",
       "      <td>106868.85</td>\n",
       "      <td>37246.34</td>\n",
       "      <td>17327.35</td>\n",
       "      <td>26740.84</td>\n",
       "      <td>...</td>\n",
       "      <td>2.32</td>\n",
       "      <td>2.35</td>\n",
       "      <td>19970708</td>\n",
       "      <td>17464.70</td>\n",
       "      <td>0.48</td>\n",
       "      <td>189.72</td>\n",
       "      <td>-44.87</td>\n",
       "      <td>17.66</td>\n",
       "      <td>-7.98</td>\n",
       "      <td>44532.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        name industry area       pe  outstanding  totals  totalAssets  \\\n",
       "code                                                                    \n",
       "300116  坚瑞沃能     电气设备   陕西     0.00        12.94   24.33   1566351.50   \n",
       "000677  恒天海龙       化纤   山东  1010.84         8.64    8.64     88712.68   \n",
       "300152  科融环境     环境保护   江苏     9.27         7.13    7.13    195568.00   \n",
       "001896  豫能控股     火力发电   河南    25.91         9.30   11.51   2086533.63   \n",
       "600095   哈高科     区域地产  黑龙江     0.00         3.61    3.61    106868.85   \n",
       "\n",
       "        liquidAssets  fixedAssets   reserved  ...  bvps     pb  timeToMarket  \\\n",
       "code                                          ...                              \n",
       "300116    1150821.75    238616.80  490198.44  ... -0.14 -14.68      20100902   \n",
       "000677      34382.80     45130.65   69768.81  ...  0.32  13.34      19961226   \n",
       "300152      99731.43     19260.75   50605.01  ...  1.11   4.12      20101229   \n",
       "001896     445571.75   1194374.75  500207.81  ...  4.99   0.81      19980122   \n",
       "600095      37246.34     17327.35   26740.84  ...  2.32   2.35      19970708   \n",
       "\n",
       "             undp  perundp     rev  profit    gpr     npr  holders  \n",
       "code                                                                \n",
       "300116 -766017.63    -3.15  -90.40  -69.27  24.37 -503.89  68130.0  \n",
       "000677 -149955.27    -1.74   32.77  287.29  15.19    0.45  77347.0  \n",
       "300152  -47372.92    -0.66    0.43  686.96  19.61  113.39  61878.0  \n",
       "001896  -58811.98    -0.51  -21.34  122.22   9.47    2.36  34008.0  \n",
       "600095   17464.70     0.48  189.72  -44.87  17.66   -7.98  44532.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "basic_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "basic_df_reset = basic_df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>area</th>\n",
       "      <th>pe</th>\n",
       "      <th>outstanding</th>\n",
       "      <th>totals</th>\n",
       "      <th>totalAssets</th>\n",
       "      <th>liquidAssets</th>\n",
       "      <th>fixedAssets</th>\n",
       "      <th>...</th>\n",
       "      <th>bvps</th>\n",
       "      <th>pb</th>\n",
       "      <th>timeToMarket</th>\n",
       "      <th>undp</th>\n",
       "      <th>perundp</th>\n",
       "      <th>rev</th>\n",
       "      <th>profit</th>\n",
       "      <th>gpr</th>\n",
       "      <th>npr</th>\n",
       "      <th>holders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>300116</td>\n",
       "      <td>坚瑞沃能</td>\n",
       "      <td>电气设备</td>\n",
       "      <td>陕西</td>\n",
       "      <td>0.00</td>\n",
       "      <td>12.94</td>\n",
       "      <td>24.33</td>\n",
       "      <td>1566351.50</td>\n",
       "      <td>1150821.75</td>\n",
       "      <td>238616.80</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.14</td>\n",
       "      <td>-14.68</td>\n",
       "      <td>20100902</td>\n",
       "      <td>-766017.63</td>\n",
       "      <td>-3.15</td>\n",
       "      <td>-90.40</td>\n",
       "      <td>-69.27</td>\n",
       "      <td>24.37</td>\n",
       "      <td>-503.89</td>\n",
       "      <td>68130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000677</td>\n",
       "      <td>恒天海龙</td>\n",
       "      <td>化纤</td>\n",
       "      <td>山东</td>\n",
       "      <td>1010.84</td>\n",
       "      <td>8.64</td>\n",
       "      <td>8.64</td>\n",
       "      <td>88712.68</td>\n",
       "      <td>34382.80</td>\n",
       "      <td>45130.65</td>\n",
       "      <td>...</td>\n",
       "      <td>0.32</td>\n",
       "      <td>13.34</td>\n",
       "      <td>19961226</td>\n",
       "      <td>-149955.27</td>\n",
       "      <td>-1.74</td>\n",
       "      <td>32.77</td>\n",
       "      <td>287.29</td>\n",
       "      <td>15.19</td>\n",
       "      <td>0.45</td>\n",
       "      <td>77347.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>300152</td>\n",
       "      <td>科融环境</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>江苏</td>\n",
       "      <td>9.27</td>\n",
       "      <td>7.13</td>\n",
       "      <td>7.13</td>\n",
       "      <td>195568.00</td>\n",
       "      <td>99731.43</td>\n",
       "      <td>19260.75</td>\n",
       "      <td>...</td>\n",
       "      <td>1.11</td>\n",
       "      <td>4.12</td>\n",
       "      <td>20101229</td>\n",
       "      <td>-47372.92</td>\n",
       "      <td>-0.66</td>\n",
       "      <td>0.43</td>\n",
       "      <td>686.96</td>\n",
       "      <td>19.61</td>\n",
       "      <td>113.39</td>\n",
       "      <td>61878.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001896</td>\n",
       "      <td>豫能控股</td>\n",
       "      <td>火力发电</td>\n",
       "      <td>河南</td>\n",
       "      <td>25.91</td>\n",
       "      <td>9.30</td>\n",
       "      <td>11.51</td>\n",
       "      <td>2086533.63</td>\n",
       "      <td>445571.75</td>\n",
       "      <td>1194374.75</td>\n",
       "      <td>...</td>\n",
       "      <td>4.99</td>\n",
       "      <td>0.81</td>\n",
       "      <td>19980122</td>\n",
       "      <td>-58811.98</td>\n",
       "      <td>-0.51</td>\n",
       "      <td>-21.34</td>\n",
       "      <td>122.22</td>\n",
       "      <td>9.47</td>\n",
       "      <td>2.36</td>\n",
       "      <td>34008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600095</td>\n",
       "      <td>哈高科</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>黑龙江</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.61</td>\n",
       "      <td>3.61</td>\n",
       "      <td>106868.85</td>\n",
       "      <td>37246.34</td>\n",
       "      <td>17327.35</td>\n",
       "      <td>...</td>\n",
       "      <td>2.32</td>\n",
       "      <td>2.35</td>\n",
       "      <td>19970708</td>\n",
       "      <td>17464.70</td>\n",
       "      <td>0.48</td>\n",
       "      <td>189.72</td>\n",
       "      <td>-44.87</td>\n",
       "      <td>17.66</td>\n",
       "      <td>-7.98</td>\n",
       "      <td>44532.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     code  name industry area       pe  outstanding  totals  totalAssets  \\\n",
       "0  300116  坚瑞沃能     电气设备   陕西     0.00        12.94   24.33   1566351.50   \n",
       "1  000677  恒天海龙       化纤   山东  1010.84         8.64    8.64     88712.68   \n",
       "2  300152  科融环境     环境保护   江苏     9.27         7.13    7.13    195568.00   \n",
       "3  001896  豫能控股     火力发电   河南    25.91         9.30   11.51   2086533.63   \n",
       "4  600095   哈高科     区域地产  黑龙江     0.00         3.61    3.61    106868.85   \n",
       "\n",
       "   liquidAssets  fixedAssets  ...  bvps     pb  timeToMarket       undp  \\\n",
       "0    1150821.75    238616.80  ... -0.14 -14.68      20100902 -766017.63   \n",
       "1      34382.80     45130.65  ...  0.32  13.34      19961226 -149955.27   \n",
       "2      99731.43     19260.75  ...  1.11   4.12      20101229  -47372.92   \n",
       "3     445571.75   1194374.75  ...  4.99   0.81      19980122  -58811.98   \n",
       "4      37246.34     17327.35  ...  2.32   2.35      19970708   17464.70   \n",
       "\n",
       "   perundp     rev  profit    gpr     npr  holders  \n",
       "0    -3.15  -90.40  -69.27  24.37 -503.89  68130.0  \n",
       "1    -1.74   32.77  287.29  15.19    0.45  77347.0  \n",
       "2    -0.66    0.43  686.96  19.61  113.39  61878.0  \n",
       "3    -0.51  -21.34  122.22   9.47    2.36  34008.0  \n",
       "4     0.48  189.72  -44.87  17.66   -7.98  44532.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "basic_df_reset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "zg_df = basic_df_reset[basic_df_reset['code'].isin(zg_list)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>area</th>\n",
       "      <th>pe</th>\n",
       "      <th>outstanding</th>\n",
       "      <th>totals</th>\n",
       "      <th>totalAssets</th>\n",
       "      <th>liquidAssets</th>\n",
       "      <th>fixedAssets</th>\n",
       "      <th>...</th>\n",
       "      <th>bvps</th>\n",
       "      <th>pb</th>\n",
       "      <th>timeToMarket</th>\n",
       "      <th>undp</th>\n",
       "      <th>perundp</th>\n",
       "      <th>rev</th>\n",
       "      <th>profit</th>\n",
       "      <th>gpr</th>\n",
       "      <th>npr</th>\n",
       "      <th>holders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>002370</td>\n",
       "      <td>亚太药业</td>\n",
       "      <td>化学制药</td>\n",
       "      <td>浙江</td>\n",
       "      <td>42.56</td>\n",
       "      <td>4.08</td>\n",
       "      <td>5.36</td>\n",
       "      <td>358351.47</td>\n",
       "      <td>139768.77</td>\n",
       "      <td>39471.57</td>\n",
       "      <td>...</td>\n",
       "      <td>4.79</td>\n",
       "      <td>3.76</td>\n",
       "      <td>20100316</td>\n",
       "      <td>66509.30</td>\n",
       "      <td>1.24</td>\n",
       "      <td>14.71</td>\n",
       "      <td>-13.28</td>\n",
       "      <td>38.51</td>\n",
       "      <td>15.51</td>\n",
       "      <td>13554.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>002865</td>\n",
       "      <td>钧达股份</td>\n",
       "      <td>汽车配件</td>\n",
       "      <td>海南</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.38</td>\n",
       "      <td>1.22</td>\n",
       "      <td>188553.88</td>\n",
       "      <td>101527.11</td>\n",
       "      <td>37024.60</td>\n",
       "      <td>...</td>\n",
       "      <td>6.86</td>\n",
       "      <td>3.10</td>\n",
       "      <td>20170425</td>\n",
       "      <td>31624.21</td>\n",
       "      <td>2.60</td>\n",
       "      <td>-46.04</td>\n",
       "      <td>-324.05</td>\n",
       "      <td>11.07</td>\n",
       "      <td>-21.88</td>\n",
       "      <td>15562.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>002823</td>\n",
       "      <td>凯中精密</td>\n",
       "      <td>机械基件</td>\n",
       "      <td>深圳</td>\n",
       "      <td>44.71</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.91</td>\n",
       "      <td>290706.91</td>\n",
       "      <td>110901.63</td>\n",
       "      <td>122522.60</td>\n",
       "      <td>...</td>\n",
       "      <td>4.22</td>\n",
       "      <td>2.72</td>\n",
       "      <td>20161124</td>\n",
       "      <td>51572.82</td>\n",
       "      <td>1.77</td>\n",
       "      <td>36.55</td>\n",
       "      <td>-49.13</td>\n",
       "      <td>22.75</td>\n",
       "      <td>3.86</td>\n",
       "      <td>17767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>603517</td>\n",
       "      <td>绝味食品</td>\n",
       "      <td>食品</td>\n",
       "      <td>湖南</td>\n",
       "      <td>26.35</td>\n",
       "      <td>1.64</td>\n",
       "      <td>4.10</td>\n",
       "      <td>497797.69</td>\n",
       "      <td>255355.80</td>\n",
       "      <td>104238.86</td>\n",
       "      <td>...</td>\n",
       "      <td>8.08</td>\n",
       "      <td>5.77</td>\n",
       "      <td>20170317</td>\n",
       "      <td>189023.77</td>\n",
       "      <td>4.61</td>\n",
       "      <td>19.63</td>\n",
       "      <td>20.38</td>\n",
       "      <td>33.31</td>\n",
       "      <td>15.71</td>\n",
       "      <td>7829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>603668</td>\n",
       "      <td>天马科技</td>\n",
       "      <td>饲料</td>\n",
       "      <td>福建</td>\n",
       "      <td>51.59</td>\n",
       "      <td>2.03</td>\n",
       "      <td>3.17</td>\n",
       "      <td>217281.22</td>\n",
       "      <td>156485.25</td>\n",
       "      <td>41197.48</td>\n",
       "      <td>...</td>\n",
       "      <td>3.14</td>\n",
       "      <td>2.94</td>\n",
       "      <td>20170117</td>\n",
       "      <td>34508.59</td>\n",
       "      <td>1.09</td>\n",
       "      <td>16.41</td>\n",
       "      <td>-18.26</td>\n",
       "      <td>15.71</td>\n",
       "      <td>4.00</td>\n",
       "      <td>21640.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       code  name industry area     pe  outstanding  totals  totalAssets  \\\n",
       "39   002370  亚太药业     化学制药   浙江  42.56         4.08    5.36    358351.47   \n",
       "43   002865  钧达股份     汽车配件   海南   0.00         0.38    1.22    188553.88   \n",
       "152  002823  凯中精密     机械基件   深圳  44.71         1.00    2.91    290706.91   \n",
       "203  603517  绝味食品       食品   湖南  26.35         1.64    4.10    497797.69   \n",
       "206  603668  天马科技       饲料   福建  51.59         2.03    3.17    217281.22   \n",
       "\n",
       "     liquidAssets  fixedAssets  ...  bvps    pb  timeToMarket       undp  \\\n",
       "39      139768.77     39471.57  ...  4.79  3.76      20100316   66509.30   \n",
       "43      101527.11     37024.60  ...  6.86  3.10      20170425   31624.21   \n",
       "152     110901.63    122522.60  ...  4.22  2.72      20161124   51572.82   \n",
       "203     255355.80    104238.86  ...  8.08  5.77      20170317  189023.77   \n",
       "206     156485.25     41197.48  ...  3.14  2.94      20170117   34508.59   \n",
       "\n",
       "     perundp    rev  profit    gpr    npr  holders  \n",
       "39      1.24  14.71  -13.28  38.51  15.51  13554.0  \n",
       "43      2.60 -46.04 -324.05  11.07 -21.88  15562.0  \n",
       "152     1.77  36.55  -49.13  22.75   3.86  17767.0  \n",
       "203     4.61  19.63   20.38  33.31  15.71   7829.0  \n",
       "206     1.09  16.41  -18.26  15.71   4.00  21640.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "156"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(zg_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16.904038461538462"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df['outstanding'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.38"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df['outstanding'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df['outstanding'].idxmin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code                  002865\n",
       "name                    钧达股份\n",
       "industry                汽车配件\n",
       "area                      海南\n",
       "pe                         0\n",
       "outstanding             0.38\n",
       "totals                  1.22\n",
       "totalAssets           188554\n",
       "liquidAssets          101527\n",
       "fixedAssets          37024.6\n",
       "reserved             38567.8\n",
       "reservedPerShare        3.17\n",
       "esp                   -0.266\n",
       "bvps                    6.86\n",
       "pb                       3.1\n",
       "timeToMarket        20170425\n",
       "undp                 31624.2\n",
       "perundp                  2.6\n",
       "rev                   -46.04\n",
       "profit               -324.05\n",
       "gpr                    11.07\n",
       "npr                   -21.88\n",
       "holders                15562\n",
       "Name: 43, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df.loc[43]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (1366, \"Incorrect string value: '\\\\xD6\\\\xD0\\\\xB9\\\\xFA\\\\xB1\\\\xEA...' for column 'VARIABLE_VALUE' at row 480\")\n",
      "  result = self._query(query)\n"
     ]
    }
   ],
   "source": [
    "engine_daily = get_engine('db_daily')\n",
    "price_df = pd.read_sql('2019-05-07',con=engine_daily)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>changepercent</th>\n",
       "      <th>trade</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>settlement</th>\n",
       "      <th>volume</th>\n",
       "      <th>turnoverratio</th>\n",
       "      <th>amount</th>\n",
       "      <th>per</th>\n",
       "      <th>pb</th>\n",
       "      <th>mktcap</th>\n",
       "      <th>nmc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>603999</td>\n",
       "      <td>读者传媒</td>\n",
       "      <td>1.460</td>\n",
       "      <td>5.56</td>\n",
       "      <td>5.53</td>\n",
       "      <td>5.58</td>\n",
       "      <td>5.43</td>\n",
       "      <td>5.48</td>\n",
       "      <td>3359170.0</td>\n",
       "      <td>0.58</td>\n",
       "      <td>18514861.0</td>\n",
       "      <td>79.43</td>\n",
       "      <td>1.90</td>\n",
       "      <td>3.202560e+05</td>\n",
       "      <td>3.202560e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>603998</td>\n",
       "      <td>方盛制药</td>\n",
       "      <td>8.407</td>\n",
       "      <td>9.80</td>\n",
       "      <td>9.22</td>\n",
       "      <td>9.94</td>\n",
       "      <td>9.20</td>\n",
       "      <td>9.04</td>\n",
       "      <td>43037343.0</td>\n",
       "      <td>10.12</td>\n",
       "      <td>415992580.0</td>\n",
       "      <td>57.65</td>\n",
       "      <td>3.97</td>\n",
       "      <td>4.278903e+05</td>\n",
       "      <td>4.166928e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>603997</td>\n",
       "      <td>继峰股份</td>\n",
       "      <td>3.193</td>\n",
       "      <td>9.05</td>\n",
       "      <td>8.85</td>\n",
       "      <td>9.20</td>\n",
       "      <td>8.82</td>\n",
       "      <td>8.77</td>\n",
       "      <td>3838299.0</td>\n",
       "      <td>0.61</td>\n",
       "      <td>34615531.0</td>\n",
       "      <td>18.98</td>\n",
       "      <td>2.99</td>\n",
       "      <td>5.786689e+05</td>\n",
       "      <td>5.701500e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>603996</td>\n",
       "      <td>中新科技</td>\n",
       "      <td>2.782</td>\n",
       "      <td>7.39</td>\n",
       "      <td>7.22</td>\n",
       "      <td>7.47</td>\n",
       "      <td>7.21</td>\n",
       "      <td>7.19</td>\n",
       "      <td>6499731.0</td>\n",
       "      <td>2.17</td>\n",
       "      <td>47818285.0</td>\n",
       "      <td>-28.42</td>\n",
       "      <td>1.64</td>\n",
       "      <td>2.218109e+05</td>\n",
       "      <td>2.218109e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>603993</td>\n",
       "      <td>洛阳钼业</td>\n",
       "      <td>1.535</td>\n",
       "      <td>3.97</td>\n",
       "      <td>3.95</td>\n",
       "      <td>4.01</td>\n",
       "      <td>3.90</td>\n",
       "      <td>3.91</td>\n",
       "      <td>77051549.0</td>\n",
       "      <td>0.44</td>\n",
       "      <td>305448553.0</td>\n",
       "      <td>18.46</td>\n",
       "      <td>2.10</td>\n",
       "      <td>8.574899e+06</td>\n",
       "      <td>7.013312e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index    code  name  changepercent  trade  open  high   low  settlement  \\\n",
       "0      0  603999  读者传媒          1.460   5.56  5.53  5.58  5.43        5.48   \n",
       "1      1  603998  方盛制药          8.407   9.80  9.22  9.94  9.20        9.04   \n",
       "2      2  603997  继峰股份          3.193   9.05  8.85  9.20  8.82        8.77   \n",
       "3      3  603996  中新科技          2.782   7.39  7.22  7.47  7.21        7.19   \n",
       "4      4  603993  洛阳钼业          1.535   3.97  3.95  4.01  3.90        3.91   \n",
       "\n",
       "       volume  turnoverratio       amount    per    pb        mktcap  \\\n",
       "0   3359170.0           0.58   18514861.0  79.43  1.90  3.202560e+05   \n",
       "1  43037343.0          10.12  415992580.0  57.65  3.97  4.278903e+05   \n",
       "2   3838299.0           0.61   34615531.0  18.98  2.99  5.786689e+05   \n",
       "3   6499731.0           2.17   47818285.0 -28.42  1.64  2.218109e+05   \n",
       "4  77051549.0           0.44  305448553.0  18.46  2.10  8.574899e+06   \n",
       "\n",
       "            nmc  \n",
       "0  3.202560e+05  \n",
       "1  4.166928e+05  \n",
       "2  5.701500e+05  \n",
       "3  2.218109e+05  \n",
       "4  7.013312e+06  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>trade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>603989</td>\n",
       "      <td>18.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>603897</td>\n",
       "      <td>20.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>603883</td>\n",
       "      <td>60.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>603876</td>\n",
       "      <td>15.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>603822</td>\n",
       "      <td>24.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>603817</td>\n",
       "      <td>6.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>603816</td>\n",
       "      <td>46.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>603797</td>\n",
       "      <td>13.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>603738</td>\n",
       "      <td>12.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>603677</td>\n",
       "      <td>13.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>603668</td>\n",
       "      <td>9.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>603626</td>\n",
       "      <td>8.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>603618</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>603601</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>603588</td>\n",
       "      <td>10.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>603569</td>\n",
       "      <td>11.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>603568</td>\n",
       "      <td>24.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>603518</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>603517</td>\n",
       "      <td>46.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>603345</td>\n",
       "      <td>45.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>603323</td>\n",
       "      <td>6.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>603313</td>\n",
       "      <td>23.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>603305</td>\n",
       "      <td>24.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>603233</td>\n",
       "      <td>48.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>603225</td>\n",
       "      <td>13.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>603179</td>\n",
       "      <td>14.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>404</th>\n",
       "      <td>603081</td>\n",
       "      <td>12.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>603055</td>\n",
       "      <td>10.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>603027</td>\n",
       "      <td>23.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>603023</td>\n",
       "      <td>5.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2709</th>\n",
       "      <td>002439</td>\n",
       "      <td>24.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2757</th>\n",
       "      <td>002391</td>\n",
       "      <td>13.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2778</th>\n",
       "      <td>002370</td>\n",
       "      <td>18.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2823</th>\n",
       "      <td>002325</td>\n",
       "      <td>3.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2830</th>\n",
       "      <td>002318</td>\n",
       "      <td>7.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2864</th>\n",
       "      <td>002284</td>\n",
       "      <td>4.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2869</th>\n",
       "      <td>002279</td>\n",
       "      <td>7.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2875</th>\n",
       "      <td>002273</td>\n",
       "      <td>12.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2877</th>\n",
       "      <td>002271</td>\n",
       "      <td>19.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2890</th>\n",
       "      <td>002258</td>\n",
       "      <td>15.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2902</th>\n",
       "      <td>002245</td>\n",
       "      <td>4.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2923</th>\n",
       "      <td>002224</td>\n",
       "      <td>6.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2968</th>\n",
       "      <td>002179</td>\n",
       "      <td>41.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>002142</td>\n",
       "      <td>22.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3008</th>\n",
       "      <td>002139</td>\n",
       "      <td>5.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3016</th>\n",
       "      <td>002131</td>\n",
       "      <td>2.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3047</th>\n",
       "      <td>002100</td>\n",
       "      <td>9.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3069</th>\n",
       "      <td>002078</td>\n",
       "      <td>6.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3134</th>\n",
       "      <td>002013</td>\n",
       "      <td>7.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3139</th>\n",
       "      <td>002008</td>\n",
       "      <td>38.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3148</th>\n",
       "      <td>001965</td>\n",
       "      <td>8.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3237</th>\n",
       "      <td>000887</td>\n",
       "      <td>10.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3252</th>\n",
       "      <td>000861</td>\n",
       "      <td>2.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3283</th>\n",
       "      <td>000811</td>\n",
       "      <td>8.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3305</th>\n",
       "      <td>000783</td>\n",
       "      <td>7.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3360</th>\n",
       "      <td>000700</td>\n",
       "      <td>3.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>000665</td>\n",
       "      <td>8.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3413</th>\n",
       "      <td>000623</td>\n",
       "      <td>17.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3556</th>\n",
       "      <td>000070</td>\n",
       "      <td>13.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3609</th>\n",
       "      <td>000001</td>\n",
       "      <td>12.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>156 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        code  trade\n",
       "7     603989  18.13\n",
       "51    603897  20.54\n",
       "60    603883  60.29\n",
       "67    603876  15.84\n",
       "88    603822  24.92\n",
       "91    603817   6.86\n",
       "92    603816  46.78\n",
       "104   603797  13.28\n",
       "120   603738  12.89\n",
       "158   603677  13.21\n",
       "161   603668   9.26\n",
       "183   603626   8.65\n",
       "185   603618   5.99\n",
       "198   603601   8.99\n",
       "206   603588  10.16\n",
       "215   603569  11.55\n",
       "216   603568  24.38\n",
       "232   603518  13.50\n",
       "233   603517  46.59\n",
       "282   603345  45.58\n",
       "295   603323   6.85\n",
       "304   603313  23.31\n",
       "309   603305  24.35\n",
       "330   603233  48.60\n",
       "336   603225  13.00\n",
       "358   603179  14.78\n",
       "404   603081  12.45\n",
       "419   603055  10.24\n",
       "437   603027  23.16\n",
       "440   603023   5.38\n",
       "...      ...    ...\n",
       "2709  002439  24.67\n",
       "2757  002391  13.54\n",
       "2778  002370  18.02\n",
       "2823  002325   3.33\n",
       "2830  002318   7.03\n",
       "2864  002284   4.96\n",
       "2869  002279   7.04\n",
       "2875  002273  12.28\n",
       "2877  002271  19.88\n",
       "2890  002258  15.11\n",
       "2902  002245   4.54\n",
       "2923  002224   6.23\n",
       "2968  002179  41.40\n",
       "3005  002142  22.16\n",
       "3008  002139   5.96\n",
       "3016  002131   2.07\n",
       "3047  002100   9.18\n",
       "3069  002078   6.56\n",
       "3134  002013   7.14\n",
       "3139  002008  38.31\n",
       "3148  001965   8.10\n",
       "3237  000887  10.38\n",
       "3252  000861   2.58\n",
       "3283  000811   8.23\n",
       "3305  000783   7.49\n",
       "3360  000700   3.71\n",
       "3388  000665   8.05\n",
       "3413  000623  17.04\n",
       "3556  000070  13.40\n",
       "3609  000001  12.95\n",
       "\n",
       "[156 rows x 2 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_df[price_df['code'].isin(zg_list)][['code','trade']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# liutong 实时的流通市值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:362: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[key] = _infer_fill_value(value)\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "for i in zg_df.index:\n",
    "    p=price_df[price_df['code']==zg_df.loc[i]['code']]['trade'].values[0]\n",
    "    ltgb=zg_df.loc[i]['outstanding']\n",
    "    t=p*ltgb\n",
    "    zg_df.loc[i,'liutong']=t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\python3_64\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "zg_df['liutong']=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df.loc[1175,'price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>area</th>\n",
       "      <th>pe</th>\n",
       "      <th>outstanding</th>\n",
       "      <th>totals</th>\n",
       "      <th>totalAssets</th>\n",
       "      <th>liquidAssets</th>\n",
       "      <th>fixedAssets</th>\n",
       "      <th>...</th>\n",
       "      <th>pb</th>\n",
       "      <th>timeToMarket</th>\n",
       "      <th>undp</th>\n",
       "      <th>perundp</th>\n",
       "      <th>rev</th>\n",
       "      <th>profit</th>\n",
       "      <th>gpr</th>\n",
       "      <th>npr</th>\n",
       "      <th>holders</th>\n",
       "      <th>liutong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>002370</td>\n",
       "      <td>亚太药业</td>\n",
       "      <td>化学制药</td>\n",
       "      <td>浙江</td>\n",
       "      <td>42.56</td>\n",
       "      <td>4.08</td>\n",
       "      <td>5.36</td>\n",
       "      <td>358351.47</td>\n",
       "      <td>139768.77</td>\n",
       "      <td>39471.57</td>\n",
       "      <td>...</td>\n",
       "      <td>3.76</td>\n",
       "      <td>20100316</td>\n",
       "      <td>66509.30</td>\n",
       "      <td>1.24</td>\n",
       "      <td>14.71</td>\n",
       "      <td>-13.28</td>\n",
       "      <td>38.51</td>\n",
       "      <td>15.51</td>\n",
       "      <td>13554.0</td>\n",
       "      <td>73.5216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>002865</td>\n",
       "      <td>钧达股份</td>\n",
       "      <td>汽车配件</td>\n",
       "      <td>海南</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.38</td>\n",
       "      <td>1.22</td>\n",
       "      <td>188553.88</td>\n",
       "      <td>101527.11</td>\n",
       "      <td>37024.60</td>\n",
       "      <td>...</td>\n",
       "      <td>3.10</td>\n",
       "      <td>20170425</td>\n",
       "      <td>31624.21</td>\n",
       "      <td>2.60</td>\n",
       "      <td>-46.04</td>\n",
       "      <td>-324.05</td>\n",
       "      <td>11.07</td>\n",
       "      <td>-21.88</td>\n",
       "      <td>15562.0</td>\n",
       "      <td>8.0636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>002823</td>\n",
       "      <td>凯中精密</td>\n",
       "      <td>机械基件</td>\n",
       "      <td>深圳</td>\n",
       "      <td>44.71</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.91</td>\n",
       "      <td>290706.91</td>\n",
       "      <td>110901.63</td>\n",
       "      <td>122522.60</td>\n",
       "      <td>...</td>\n",
       "      <td>2.72</td>\n",
       "      <td>20161124</td>\n",
       "      <td>51572.82</td>\n",
       "      <td>1.77</td>\n",
       "      <td>36.55</td>\n",
       "      <td>-49.13</td>\n",
       "      <td>22.75</td>\n",
       "      <td>3.86</td>\n",
       "      <td>17767.0</td>\n",
       "      <td>11.4700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>603517</td>\n",
       "      <td>绝味食品</td>\n",
       "      <td>食品</td>\n",
       "      <td>湖南</td>\n",
       "      <td>26.35</td>\n",
       "      <td>1.64</td>\n",
       "      <td>4.10</td>\n",
       "      <td>497797.69</td>\n",
       "      <td>255355.80</td>\n",
       "      <td>104238.86</td>\n",
       "      <td>...</td>\n",
       "      <td>5.77</td>\n",
       "      <td>20170317</td>\n",
       "      <td>189023.77</td>\n",
       "      <td>4.61</td>\n",
       "      <td>19.63</td>\n",
       "      <td>20.38</td>\n",
       "      <td>33.31</td>\n",
       "      <td>15.71</td>\n",
       "      <td>7829.0</td>\n",
       "      <td>76.4076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>603668</td>\n",
       "      <td>天马科技</td>\n",
       "      <td>饲料</td>\n",
       "      <td>福建</td>\n",
       "      <td>51.59</td>\n",
       "      <td>2.03</td>\n",
       "      <td>3.17</td>\n",
       "      <td>217281.22</td>\n",
       "      <td>156485.25</td>\n",
       "      <td>41197.48</td>\n",
       "      <td>...</td>\n",
       "      <td>2.94</td>\n",
       "      <td>20170117</td>\n",
       "      <td>34508.59</td>\n",
       "      <td>1.09</td>\n",
       "      <td>16.41</td>\n",
       "      <td>-18.26</td>\n",
       "      <td>15.71</td>\n",
       "      <td>4.00</td>\n",
       "      <td>21640.0</td>\n",
       "      <td>18.7978</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       code  name industry area     pe  outstanding  totals  totalAssets  \\\n",
       "39   002370  亚太药业     化学制药   浙江  42.56         4.08    5.36    358351.47   \n",
       "43   002865  钧达股份     汽车配件   海南   0.00         0.38    1.22    188553.88   \n",
       "152  002823  凯中精密     机械基件   深圳  44.71         1.00    2.91    290706.91   \n",
       "203  603517  绝味食品       食品   湖南  26.35         1.64    4.10    497797.69   \n",
       "206  603668  天马科技       饲料   福建  51.59         2.03    3.17    217281.22   \n",
       "\n",
       "     liquidAssets  fixedAssets  ...    pb  timeToMarket       undp  perundp  \\\n",
       "39      139768.77     39471.57  ...  3.76      20100316   66509.30     1.24   \n",
       "43      101527.11     37024.60  ...  3.10      20170425   31624.21     2.60   \n",
       "152     110901.63    122522.60  ...  2.72      20161124   51572.82     1.77   \n",
       "203     255355.80    104238.86  ...  5.77      20170317  189023.77     4.61   \n",
       "206     156485.25     41197.48  ...  2.94      20170117   34508.59     1.09   \n",
       "\n",
       "       rev  profit    gpr    npr  holders  liutong  \n",
       "39   14.71  -13.28  38.51  15.51  13554.0  73.5216  \n",
       "43  -46.04 -324.05  11.07 -21.88  15562.0   8.0636  \n",
       "152  36.55  -49.13  22.75   3.86  17767.0  11.4700  \n",
       "203  19.63   20.38  33.31  15.71   7829.0  76.4076  \n",
       "206  16.41  -18.26  15.71   4.00  21640.0  18.7978  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zg_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'price'",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2656\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2657\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2658\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'price'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-22-07914b0f2945>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mdel\u001b[0m \u001b[0mzg_df\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'price'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m__delitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   3313\u001b[0m             \u001b[1;31m# there was no match, this call should raise the appropriate\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3314\u001b[0m             \u001b[1;31m# exception:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3315\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdelete\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3316\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3317\u001b[0m         \u001b[1;31m# delete from the caches\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mdelete\u001b[1;34m(self, item)\u001b[0m\n\u001b[0;32m    983\u001b[0m         \u001b[0mDelete\u001b[0m \u001b[0mselected\u001b[0m \u001b[0mitem\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mitems\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mnon\u001b[0m\u001b[1;33m-\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32min\u001b[0m\u001b[1;33m-\u001b[0m\u001b[0mplace\u001b[0m\u001b[1;33m.\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    984\u001b[0m         \"\"\"\n\u001b[1;32m--> 985\u001b[1;33m         \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    986\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    987\u001b[0m         \u001b[0mis_deleted\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mzeros\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbool_\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2657\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2658\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2659\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2660\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2661\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msize\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'price'"
     ],
     "output_type": "error"
    }
   ],
   "source": [
    "del zg_df['price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = zg_df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>area</th>\n",
       "      <th>pe</th>\n",
       "      <th>outstanding</th>\n",
       "      <th>totals</th>\n",
       "      <th>totalAssets</th>\n",
       "      <th>liquidAssets</th>\n",
       "      <th>fixedAssets</th>\n",
       "      <th>...</th>\n",
       "      <th>pb</th>\n",
       "      <th>timeToMarket</th>\n",
       "      <th>undp</th>\n",
       "      <th>perundp</th>\n",
       "      <th>rev</th>\n",
       "      <th>profit</th>\n",
       "      <th>gpr</th>\n",
       "      <th>npr</th>\n",
       "      <th>holders</th>\n",
       "      <th>liutong</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>002370</td>\n",
       "      <td>亚太药业</td>\n",
       "      <td>化学制药</td>\n",
       "      <td>浙江</td>\n",
       "      <td>42.56</td>\n",
       "      <td>4.08</td>\n",
       "      <td>5.36</td>\n",
       "      <td>358351.47</td>\n",
       "      <td>139768.77</td>\n",
       "      <td>39471.57</td>\n",
       "      <td>...</td>\n",
       "      <td>3.76</td>\n",
       "      <td>20100316</td>\n",
       "      <td>66509.30</td>\n",
       "      <td>1.24</td>\n",
       "      <td>14.71</td>\n",
       "      <td>-13.28</td>\n",
       "      <td>38.51</td>\n",
       "      <td>15.51</td>\n",
       "      <td>13554.0</td>\n",
       "      <td>73.5216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>002865</td>\n",
       "      <td>钧达股份</td>\n",
       "      <td>汽车配件</td>\n",
       "      <td>海南</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.38</td>\n",
       "      <td>1.22</td>\n",
       "      <td>188553.88</td>\n",
       "      <td>101527.11</td>\n",
       "      <td>37024.60</td>\n",
       "      <td>...</td>\n",
       "      <td>3.10</td>\n",
       "      <td>20170425</td>\n",
       "      <td>31624.21</td>\n",
       "      <td>2.60</td>\n",
       "      <td>-46.04</td>\n",
       "      <td>-324.05</td>\n",
       "      <td>11.07</td>\n",
       "      <td>-21.88</td>\n",
       "      <td>15562.0</td>\n",
       "      <td>8.0636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>002823</td>\n",
       "      <td>凯中精密</td>\n",
       "      <td>机械基件</td>\n",
       "      <td>深圳</td>\n",
       "      <td>44.71</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.91</td>\n",
       "      <td>290706.91</td>\n",
       "      <td>110901.63</td>\n",
       "      <td>122522.60</td>\n",
       "      <td>...</td>\n",
       "      <td>2.72</td>\n",
       "      <td>20161124</td>\n",
       "      <td>51572.82</td>\n",
       "      <td>1.77</td>\n",
       "      <td>36.55</td>\n",
       "      <td>-49.13</td>\n",
       "      <td>22.75</td>\n",
       "      <td>3.86</td>\n",
       "      <td>17767.0</td>\n",
       "      <td>11.4700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>603517</td>\n",
       "      <td>绝味食品</td>\n",
       "      <td>食品</td>\n",
       "      <td>湖南</td>\n",
       "      <td>26.35</td>\n",
       "      <td>1.64</td>\n",
       "      <td>4.10</td>\n",
       "      <td>497797.69</td>\n",
       "      <td>255355.80</td>\n",
       "      <td>104238.86</td>\n",
       "      <td>...</td>\n",
       "      <td>5.77</td>\n",
       "      <td>20170317</td>\n",
       "      <td>189023.77</td>\n",
       "      <td>4.61</td>\n",
       "      <td>19.63</td>\n",
       "      <td>20.38</td>\n",
       "      <td>33.31</td>\n",
       "      <td>15.71</td>\n",
       "      <td>7829.0</td>\n",
       "      <td>76.4076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>603668</td>\n",
       "      <td>天马科技</td>\n",
       "      <td>饲料</td>\n",
       "      <td>福建</td>\n",
       "      <td>51.59</td>\n",
       "      <td>2.03</td>\n",
       "      <td>3.17</td>\n",
       "      <td>217281.22</td>\n",
       "      <td>156485.25</td>\n",
       "      <td>41197.48</td>\n",
       "      <td>...</td>\n",
       "      <td>2.94</td>\n",
       "      <td>20170117</td>\n",
       "      <td>34508.59</td>\n",
       "      <td>1.09</td>\n",
       "      <td>16.41</td>\n",
       "      <td>-18.26</td>\n",
       "      <td>15.71</td>\n",
       "      <td>4.00</td>\n",
       "      <td>21640.0</td>\n",
       "      <td>18.7978</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     code  name industry area     pe  outstanding  totals  totalAssets  \\\n",
       "0  002370  亚太药业     化学制药   浙江  42.56         4.08    5.36    358351.47   \n",
       "1  002865  钧达股份     汽车配件   海南   0.00         0.38    1.22    188553.88   \n",
       "2  002823  凯中精密     机械基件   深圳  44.71         1.00    2.91    290706.91   \n",
       "3  603517  绝味食品       食品   湖南  26.35         1.64    4.10    497797.69   \n",
       "4  603668  天马科技       饲料   福建  51.59         2.03    3.17    217281.22   \n",
       "\n",
       "   liquidAssets  fixedAssets  ...    pb  timeToMarket       undp  perundp  \\\n",
       "0     139768.77     39471.57  ...  3.76      20100316   66509.30     1.24   \n",
       "1     101527.11     37024.60  ...  3.10      20170425   31624.21     2.60   \n",
       "2     110901.63    122522.60  ...  2.72      20161124   51572.82     1.77   \n",
       "3     255355.80    104238.86  ...  5.77      20170317  189023.77     4.61   \n",
       "4     156485.25     41197.48  ...  2.94      20170117   34508.59     1.09   \n",
       "\n",
       "     rev  profit    gpr    npr  holders  liutong  \n",
       "0  14.71  -13.28  38.51  15.51  13554.0  73.5216  \n",
       "1 -46.04 -324.05  11.07 -21.88  15562.0   8.0636  \n",
       "2  36.55  -49.13  22.75   3.86  17767.0  11.4700  \n",
       "3  19.63   20.38  33.31  15.71   7829.0  76.4076  \n",
       "4  16.41  -18.26  15.71   4.00  21640.0  18.7978  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_sql('tb_zhenggu_liutong_2019-05-07',engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
